package shaka.db;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import shaka.db.face.IDbQryCB;
import shaka.db.internal.ConnectionManager;

public class DbEngine {
	private static final DbEngine instance = new DbEngine();
	private Log log = LogFactory.getLog(DbEngine.class);
	private ThreadLocal<Connection> _conn;
	private ThreadLocal<Statement> _stmt;
	private ThreadLocal<DatabaseMetaData> _dbMeta;
	private ThreadLocal<PreparedStatement> _pstmt;
	private static final int FETCH_SIZE = 1000;

	private DbEngine() {
		_conn = new ThreadLocal<Connection>();
		_stmt = new ThreadLocal<Statement>();
		_dbMeta = new ThreadLocal<DatabaseMetaData>();
		_pstmt = new ThreadLocal<PreparedStatement>();
	}

	/**
	 * 取出 instance
	 * 
	 * @return instance
	 */
	public static DbEngine getInstance() {
		return instance;
	}

	public void createTransaction() throws SQLException {
		if (_conn.get() != null) return;
		Connection con = ConnectionManager.getInstance().getConnection();
		if (log.isDebugEnabled()) log.debug("建立連線...");
		con.setAutoCommit(false);
		_conn.set(con);
	}

	public void commit() throws SQLException {
		if (_conn.get() != null) {
			_conn.get().commit();
		}
	}

	public void rollback() throws SQLException {
		if (_conn.get() != null) {
			_conn.get().rollback();
		}
	}

	public void close() throws SQLException {
		if (log.isDebugEnabled()) log.debug("釋放連線 & Statement ...");
		if (_dbMeta.get() != null) {
			_dbMeta.get();
			_dbMeta.remove();
		}
		if (_stmt.get() != null) {
			_stmt.get().close();
			_stmt.remove();
		}
		if (_pstmt.get() != null) {
			_pstmt.get().close();
			_pstmt.remove();
		}
		if (_conn.get() != null) {
			_conn.get().close();
			_conn.remove();
		}
	}

	public List<Map<String, Object>> query(String sql) throws SQLException {
		Connection con = getConnection();
		Statement stmt = _stmt.get();
		if (stmt == null) {
			if (log.isDebugEnabled()) log.debug("建立Statement ...");
			stmt = con.createStatement();
			_stmt.set(stmt);
		}
		ResultSet rs = stmt.executeQuery(sql);
		return processResult2List(rs);
	}

	public int update(String sql) throws SQLException {
		Connection con = getConnection();
		Statement stmt = _stmt.get();
		if (stmt == null) {
			if (log.isDebugEnabled()) log.debug("建立Statement ...");
			stmt = con.createStatement();
			_stmt.set(stmt);
		}
		return stmt.executeUpdate(sql);
	}

	public Connection getConnection() {
		Connection con = _conn.get();
		if (con == null) { throw new RuntimeException("Must create transaction first !!!"); }
		return con;
	}

	public List<String> showColumns(String tableName) throws SQLException {
		String sql = String.format(" SELECT * FROM %S WHERE 1 = 2 ", tableName);
		Connection con = getConnection();
		Statement stmt = _stmt.get();
		if (stmt == null) {
			if (log.isDebugEnabled()) log.debug("建立Statement ...");
			stmt = con.createStatement();
			_stmt.set(stmt);
		}
		ResultSet rs = stmt.executeQuery(sql);

		List<String> columnNames = new ArrayList<String>();
		ResultSetMetaData meta = rs.getMetaData();
		for (int i = 1; i <= meta.getColumnCount(); i++) {
			String columnName = meta.getColumnName(i);
			columnNames.add(columnName);
		}
		return columnNames;
	}

	public void showComment(String schema, String tableName, String columnName, IDbQryCB cb) throws SQLException {
		Connection con = getConnection();

		DatabaseMetaData ometa = _dbMeta.get();
		if (ometa == null) {
			ometa = con.getMetaData();
			_dbMeta.set(ometa);
		}
		ResultSet _rs = ometa.getColumns(null, schema, tableName, columnName);
		if (_rs.next()) {
			cb.eachRowCallback(null, _rs);
		}
	}

	private List<Map<String, Object>> processResult2List(ResultSet rs) throws SQLException {
		ResultSetMetaData meta = rs.getMetaData();

		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		while (rs.next()) {
			Map<String, Object> row = new HashMap<String, Object>();
			result.add(row);
			getXxx(rs, meta, row);
		}
		if (rs != null) {
			rs.close();
		}
		return result;
	}

	public void getXxx(ResultSet rs, ResultSetMetaData meta, Map<String, Object> row) throws SQLException {
		for (int i = 1; i <= meta.getColumnCount(); i++) {
			String columnName = meta.getColumnName(i);
			switch (meta.getColumnType(i)) {
			case Types.INTEGER:
			case Types.DECIMAL:
				row.put(columnName, rs.getInt(i));
				break;
			case Types.FLOAT:
				row.put(columnName, rs.getFloat(i));
				break;
			case Types.DOUBLE:
				row.put(columnName, rs.getDouble(i));
				break;
			case Types.NUMERIC:
				row.put(columnName, rs.getDouble(i));
				break;
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				row.put(columnName, rs.getString(i));
				break;
			case Types.DATE:
				row.put(columnName, rs.getDate(i));
				break;
			case Types.TIME:
				row.put(columnName, rs.getTime(i));
				break;
			case Types.TIMESTAMP:
				row.put(columnName, rs.getTimestamp(i));
				break;
			default:
				row.put(columnName, "unsupport type ...");
			}
		}
	}

	public String type2Str(int type) {
		switch (type) {
		case Types.ARRAY:
			return "ARRAY";
		case Types.BIGINT:
			return "BIGINT";
		case Types.BINARY:
			return "BINARY";
		case Types.BIT:
			return "BIT";
		case Types.BLOB:
			return "BLOB";
		case Types.BOOLEAN:
			return "BOOLEAN";
		case Types.CHAR:
			return "CHAR";
		case Types.CLOB:
			return "CLOB";
		case Types.DATALINK:
			return "DATALINK";
		case Types.DATE:
			return "DATE";
		case Types.DECIMAL:
			return "DECIMAL";
		case Types.DISTINCT:
			return "DISTINCT";
		case Types.DOUBLE:
			return "DOUBLE";
		case Types.FLOAT:
			return "FLOAT";
		case Types.INTEGER:
			return "INTEGER";
		case Types.JAVA_OBJECT:
			return "JAVA_OBJECT";
		case Types.LONGVARBINARY:
			return "LONGVARBINARY";
		case Types.LONGVARCHAR:
			return "LONGVARCHAR";
		case Types.NULL:
			return "NULL";
		case Types.NUMERIC:
			return "NUMERIC";
		case Types.OTHER:
			return "OTHER";
		case Types.REAL:
			return "REAL";
		case Types.REF:
			return "REF";
		case Types.SMALLINT:
			return "SMALLINT";
		case Types.STRUCT:
			return "STRUCT";
		case Types.TIME:
			return "TIME";
		case Types.TIMESTAMP:
			return "TIMESTAMP";
		case Types.TINYINT:
			return "TINYINT";
		case Types.VARBINARY:
			return "VARBINARY";
		case Types.VARCHAR:
			return "VARCHAR";
		case Types.ROWID:
			return "ROWID";
		case Types.NCHAR:
			return "NCHAR";
		case Types.NVARCHAR:
			return "NVARCHAR";
		case Types.LONGNVARCHAR:
			return "LONGNVARCHAR";
		case Types.NCLOB:
			return "NCLOB";
		case Types.SQLXML:
			return "SQLXML";
		}
		throw new RuntimeException(String.format("unknow type[%s]", type));
	}

	public void pSql(String sql) throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = _pstmt.get();
		if (stmt != null) {
			if (log.isDebugEnabled()) log.debug("清除舊的PreparedStatement ...");
			stmt.close();
		}
		if (log.isDebugEnabled()) log.debug("建立PreparedStatement ...");
		stmt = con.prepareStatement(sql);
		_pstmt.set(stmt);
	}

	public List<Map<String, Object>> pQuery() throws SQLException {
		return this.pQuery(null, null);
	}

	@SuppressWarnings("rawtypes")
	public List<Map<String, Object>> pQuery(Class[] types, Object[] values) throws SQLException {
		PreparedStatement stmt = pSetXxx(types, values);
		// stmt.setFetchSize(1000);
		ResultSet rs = stmt.executeQuery();
		return processResult2List(rs);
	}

	@SuppressWarnings("rawtypes")
	private PreparedStatement pSetXxx(Class[] types, Object[] values) throws SQLException {
		PreparedStatement stmt = _pstmt.get();
		if (stmt == null) { throw new RuntimeException("Must setting SQL up statement ..."); }
		int index = 0;
		if (types != null && values != null) {
			for (Class type : types) {
				if (type == String.class) {
					stmt.setString(index + 1, (String) values[index]);
				} else if (type == Integer.class || type == int.class) {
					stmt.setInt(index + 1, (Integer) values[index]);
				} else if (type == Long.class || type == long.class) {
					stmt.setLong(index + 1, (Long) values[index]);
				} else if (type == Float.class || type == float.class) {
					stmt.setFloat(index + 1, (Float) values[index]);
				} else if (type == Double.class || type == double.class) {
					stmt.setDouble(index + 1, (Double) values[index]);
				} else if (type == BigDecimal.class) {
					stmt.setBigDecimal(index + 1, (BigDecimal) values[index]);
				} else if (type == java.sql.Date.class) {
					stmt.setDate(index + 1, (java.sql.Date) values[index]);
				} else if (type == java.util.Date.class) {
					stmt.setDate(index + 1, new java.sql.Date(((java.sql.Date) values[index]).getTime()));
				} else if (type == java.sql.Time.class) {
					stmt.setTime(index + 1, (java.sql.Time) values[index]);
				} else if (type == java.sql.Timestamp.class) {
					stmt.setTimestamp(index + 1, (java.sql.Timestamp) values[index]);
				} else {
					throw new SQLException(String.format("Unsupport parameter type [%s] now ...", type.getName()));
				}
				index++;
			}
		}
		return stmt;
	}

	public int pUpdate() throws SQLException {
		return this.pUpdate(null, null);
	}

	@SuppressWarnings("rawtypes")
	public int pUpdate(Class[] types, Object[] values) throws SQLException {
		PreparedStatement stmt = pSetXxx(types, values);
		return stmt.executeUpdate();
	}

	public void pQuery(IDbQryCB callback) {
		this.pQuery(null, null, callback);
	}

	@SuppressWarnings("rawtypes")
	public void pQuery(Class[] types, Object[] values, IDbQryCB callback) {
		ResultSet rs = null;
		boolean hasRow = false;
		try {
			PreparedStatement stmt = pSetXxx(types, values);
			stmt.setFetchSize(FETCH_SIZE);
			rs = stmt.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();

			callback.start(); // 通知 callback 即將開始進行 result each row
			while (rs.next()) {
				hasRow = true;
				// 所有的查出資料應該要即時清除，避免佔用記憶體空間，交給 callback 自行控制
				callback.eachRowCallback(meta, rs);
			}
		} catch (SQLException e) {
			callback.error(e);
		} catch (Exception e) {
			callback.error(e);
		} finally {
			if (hasRow) {
				callback.endEachRow(); // 通知 callback 結束 result each row
			} else {
				callback.endNoRow(); // 通知 callback 結束 (查無資料)
			}
			callback.end(); // 通知 callback 結束 (無論有沒有 rows 都一定會執行)
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					callback.error(e);
				}
			}
		}
	}
}
